﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TeacherHelp.DAL
{
    public class UserService
    {
        public string GetUserInfoByLoginNameAndLoginPwdSQL(string loginName, string loginPwd)
        {
            string sql = $@"SELECT * FROM dbo.[User]
                            WHERE LoginName = '{loginName}' AND LoginPwd = '{loginPwd}'";
            return sql;
        }

        public string GetUserExistsSQL(string loginName)
        {
            string sql = $@"SELECT COUNT(0) FROM dbo.[User] WHERE LoginName = '{loginName}'";
            return sql;
        }

        public string GetUserInfoSQL()
        {
            string sql = $@"
            SELECT a.Id,
                a.Name,
                a.LoginName,
                a.LoginPwd,
                a.Sex,
                a.RoleId,
                b.Name roleName,
                d.Id gradeId,
                d.Name gradeName 
                FROM dbo.[User] a
            LEFT JOIN dbo.[Role] b ON a.RoleId = b.Id
            LEFT JOIN TeacherLinkGrade c ON a.Id = c.UserId
            LEFT JOIN dbo.Grade d  ON c.GradeId = d.Id";
            return sql;
        }

        public string GetUserInfoByUserNameSQL(string userName)
        {
            string sql = GetUserInfoSQL();
            sql += $" where a.Name like '%{userName}%'";
            return sql;

        }

        public string GetUserInfoByUserIdSQL(int userId)
        {
            string sql = GetUserInfoSQL();
            sql += $" where a.Id like '%{userId}%'";
            return sql;
        }

        public string GetIdWhereSQL(int userId)
        {
            string sql = $" WHERE Id = '{userId}' ";
            return sql;
        }

        public string GetUserIdWhereSQL(int roleId)
        {
            string sql = $" WHERE UserId = '{roleId}' ";
            return sql;
        }

        public string DeleteUserByIdSQL(int userId)
        {
            string sql = $"DELETE dbo.[User] WHERE Id = '{userId}'";
            return sql;
        }
    }
}
